import pymysql

# 建立数据库连接
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='205228ycl',
    charset='utf8'
)
cursor = conn.cursor()

# 创建数据库（如果不存在）
cursor.execute("CREATE DATABASE IF NOT EXISTS `transport`")
cursor.execute("USE `transport`")

# 创建站点表
stations_table = """
CREATE TABLE IF NOT EXISTS `stations` (
    `station_id` INT PRIMARY KEY AUTO_INCREMENT,
    `station_name` VARCHAR(100) NOT NULL,
    `x_coordinate` DECIMAL(10,6) NOT NULL,
    `y_coordinate` DECIMAL(10,6) NOT NULL,
    `station_type` ENUM('pickup', 'delivery', 'depot') NOT NULL
);
"""
cursor.execute(stations_table)

# 创建订单表
orders_table = """
CREATE TABLE IF NOT EXISTS `orders` (
    `order_id` INT PRIMARY KEY AUTO_INCREMENT,
    `order_number` VARCHAR(50) UNIQUE NOT NULL,
    `pickup_station_id` INT NOT NULL,
    `delivery_x` DECIMAL(10, 6) NOT NULL,
    `delivery_y` DECIMAL(10, 6) NOT NULL,
    `order_time` DATETIME,
    `completed_time` DATETIME,
    `vehicle_ids` VARCHAR(255),
    `weight` DECIMAL(8, 2) NOT NULL,
    `order_status` ENUM('pending', 'processing', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
);
"""
cursor.execute(orders_table)

# 创建车辆表
vehicles_table = """
CREATE TABLE IF NOT EXISTS `vehicles` (
    `vehicle_id` INT PRIMARY KEY AUTO_INCREMENT,
    `current_order_id` INT,
    `current_x` DECIMAL(10,6) NOT NULL,
    `current_y` DECIMAL(10,6) NOT NULL,
    `status` ENUM('idle', 'en_route', 'serving', 'offline') NOT NULL DEFAULT 'idle',
    `max_capacity` INT NOT NULL,
);
"""
cursor.execute(vehicles_table)

# 创建用户表
users_table = """
CREATE TABLE IF NOT EXISTS `users` (
    `user_id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `user_role` ENUM('merchant', 'courier') NOT NULL,
    `vehicle_id` INT,
    `station_id` INT,
);
"""
cursor.execute(users_table)

# 提交事务
conn.commit()
print("表创建成功！")

# 关闭游标和连接
cursor.close()
conn.close()